//(function(){

	/*
	query = "select fld1 from tbl1 union select fld2 from tbl2 where a = 2";
	query = "select _id from surveys limit 1 union select _id from surveys where _id = 4111"
	query = "select * from(select * from tbl1)x";
	console.log(query);
	console.log(SQLParser.parse(query));

	query = "select * from tbl1 a";
	console.log(query);
	console.log(SQLParser.parse(query));
	*/
	
	function printRows(r){
	
		if( r.rows == undefined ) {console.log("No result. " + r.message); return; }
		
		for(var i = 0, len = r.rows.length; i < len; i++){
			var row = r.rows.item(i)
			    ,_r   = ""//"Row: " + ((i + 1).toString())
				,fI = 0;
			for (var field in row ){
				_r = _r + (fI == 0 ? "" : "," ) + " [" + field + "] : " + row[field];
				fI++;
			}
			console.log( _r );
		}
		
	}
	
	storageDriverToUse = function(){
		return "IndexedDB";
	}
	
	var crcDB;
	function initDB(){
	
		if( crcDB ){
			return;
		}
		
		var shortName = 'crcInv1';
		var version = '1.0';
		var displayName = 'CRC Database';
		var maxSize = 1024 * 1024 * 5; // in bytes
		crcDB = openDatabase(shortName, version, displayName, maxSize);
			
	}

	/*
	query = "select * from tbl1 where a is not 1";
	var optimizer = new HPI.QueryOptimizer();
	console.log(optimizer.generatePlan(SQLParser.parse(query)));
	*/
	
	   " select distinct s.title,s.surveycode, s.mandatory  ,sas.StoreActivityID as Ans  "
	+ " from surveys s  "
	+ " inner join surveystores st on s.surveycode=st.sssurveycode  "
	+ " left join surveyanswersession sas ON sas.surveycode = s.surveycode AND sas.StoreID = 1136334 and sas.StoreActivityID = -1001 "
    + " left join schd_surveyschedule ss ON ss.surveyid = s._id  "
	+ " where  st.sschainnumber= 4516 and st.ssstorenumber = 677 "
	+ " and ss.surveyid is null  and (Datetime(s.startdate) <= Datetime('2013-06-13 20:49:00.000'))  "
	+ "group by s.title "
	+ " order by s.title COLLATE NOCASE";

var query = "select "
			+ " distinct s.title,s.surveycode, s.mandatory "
			+ " s.title,s.surveycode, s.mandatory "
			+ " from surveys s "
			+ " inner join surveystores st on s.surveycode=st.sssurveycode "
            + " left join schd_surveyschedule ss ON ss.surveyid = s._id  "
			+ " where st.sschainnumber= 4516 and st.ssstorenumber = 1392"
			+ " and (Datetime(s.startdate) <= Datetime('2013-06-13 20:49:00.000'))"
        	+ " group by s.title "
        	+ " order by s.title COLLATE NOCASE";
	
var query = "select distinct s.title,s.surveycode, s.mandatory "
			//+ " ,sas.storeactivityid as Ans "
			+ " from surveys s "
			+ " inner join surveystores st on s.surveycode=st.sssurveycode "
			//+ " left join surveyanswersession sas ON sas.surveycode = s.surveycode AND sas.StoreID = 739709 and sas.StoreActivityID = 1001"
			//+ " where  st.sschainnumber= 2416 and st.ssstorenumber = 1 and (Datetime(s.startdate) <= Datetime('2013-05-01 12:09:00.000'))"
			+ " where st.sschainnumber= 4516 and st.ssstorenumber = 1392"
			//+ " group by s.title order by s.title COLLATE NOCASE";

   "select distinct s.title,s.surveycode, s.mandatory"
//+ " ,sas.StoreActivityID as Ans "
+ " from surveys s  inner join surveystores st on s.surveycode=st.sssurveycode  "
//+ " left join surveyanswersession sas ON sas.surveycode = s.surveycode AND sas.StoreID = 159078 and sas.StoreActivityID = -1001"
//+ " left join schd_surveyschedule ss ON ss.surveyid = s._id  "
+ " where  st.sschainnumber= 15555 and st.ssstorenumber = 123 "
//+ " and ss.surveyid is null  and (Datetime(s.startdate) <= Datetime('2013-08-07 02:44:00.000'))  "
//+ " group by s.title order by s.title COLLATE NOCASE";
    var query = "select * "
    + " from surveystores st "
    + " order by st.sschainnumber, st.ssstorenumber";
	//var query = "select * from surveystores st where  st.sschainnumber= 4516 and st.ssstorenumber = 1392";
	//var query = "select ((2*3)+2)  as field1 from surveys where _id = 4111";
	//var query = "select 1 as '_id', 2 as '_id' from surveystores";
	//var query = "select * from(select _id from surveystores)x";
	//var query = "select _id, surveys.title, canaudit as 'test', enddate as test1, surveys.startdate, surveys.skipable as test2 from surveys";
	//var query = "select * from (select _id, _iD, surveys._id from surveys)";
	//var query = "select _id, _iD, _id as 'test', _id as test, surveys._id from surveys";
	//var query = "select distinct s.title,s.surveycode, s.mandatory  from surveys s  inner join surveystores st on s.surveycode=st.sssurveycode  where st.sschainnumber= 4516 and st.ssstorenumber = 1392";
	//var query = "select * from(select 1+1 as test, * from (select _id, _iD, _id as 'test', _id as test, surveys._id from surveys)x)x";
	//var query = "select * from(select _id, _iD, _id as 'test', _id as test, surveys._id from surveys limit 1)";
	//var query = "select * from(select _id as 'a', * from (select _id as 'a', 6 as 'a', * from(select 1 as 'a', 2 as 'a', 3 as 'a', * from surveys)x)x)x";

var query = "select "
+ " (select _id from surveystores where _id = s._id) as a "
+ " from surveys s";
	
1, 1, 1
2, 1, 1
2, 2, 2

// _qry("CREATE TABLE a(a1 int, a2 int)", function(){});
// _qry("CREATE TABLE b(b1 int, b2 int)", function(){});
// _qry("create table c(c1 int, c2 int)", function(t, r){});
/*
_qry("insert into a values(1,1)", function(){});
_qry("insert into b values(1,2)", function(){});

_qry("select * from a left join b on a1 = b1 and a2 = b2", function(t, r){
    console.log(r.rows);
});

_qry("explain query plan select * from a left join b on a1 = b1 where a2 = b2", function(t, r){
    console.log(r.rows);
});

_qry("select * from a left join b on a1 = b1"
+ " where b2 = 2", function(t, r){
    console.log(r.rows);
});

_qry(
  "explain query plan select * from a, b "
+ " left join c on c.c1 = a.a1 "
+ " where a1 = 2", function(t, r){
    console.log(r);
});
*/

//crcDB = null;
//initDB();

crcDB = null;
//initDB();
/*
var query = "create table if not exists a (a1 int);";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	//console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});
*/
/*
initDB();
var query = "insert into a values(1);";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	//console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});

initDB();
var query = "select * from a;";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});

initDB();
var query = "select * from a;";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});
//	return;
*/
//})();


/*

crcDB = null;
initDB();
var query ="select intValue  from preferences  where prefname='HHPCEnterCheckInOutTime'";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});

initDB();
var query ="select prefname, intvalue from preferences where prefname='GetMileageOnClockout' or prefname='GetMileageOnLogin'";
var startDate = new Date();
//DB.useOldVersion = true;
ExecuteReaderWithSingleCallback(query, crcDB, function(trx, r_e){
	var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	console.log(r_e.rows.length);
	console.log(r_e);
	//alert(duration);
	console.log(duration);
});

try{
	var query = "select * from surveys s "
	+ " inner join surveystores st on s.surveycode=st.sssurveycode "
	+ " where st.sschainnumber= 4516 and st.ssstorenumber = 1392"
	//var query = "select _id, _iD, _id as 'test', _id as test, surveys._id from surveys";
	DB.useOldVersion = false;
	var pt = SQLParser.parse(query);
	var  result = {newParseTree : {}, executionPlan : []};
	var optimizer = new HPI.QueryOptimizer({storageEngine : DB.getStorageDriver()});
    optimizer.generatePlan(pt, result);
    console.log(pt);
    console.log(query);
    console.log(result.newParseTree);
	console.log(result.executionPlan);
}
catch(e){
	console.log(pt);
    console.log(query);
	console.log(e);
}


todo: 
- implement a centralized checking of expression
    - valid field reference
	- agg operators are not misused
- early bail-out if WHERE condition is a FALSE literal. - done
- remove in otherCond array any TRUE literal - done


- add checking to ban aggregate functions in GROUP BY clause. - done
- select a2 from a order by sum(a1+1); 
		--should result in error, because there's no GROUP BY clause, or no DISTINCT clause, or no mention of AGG. fn in SELECT
- implement having


---Future Test Cases
check if agg operators are not allowed in:
   ON clause
   WHERE clause
   GROUP BY clause
   ORDER BY clause with no GROUP BY or DISTINCT or no MENTION of at least one AGG operator in SELECT
   nested AGG operators
   
   
+ " where  st.sschainnumber= 4516 and st.ssstorenumber = 677 "
+ " and ss.surveyid is null  "
+ " and (Datetime(s.startdate) <= Datetime('2013-06-13 20:49:00.000'))  " -->this condition causes runtime error.


query: select * from surveys s inner join surveystores s on s.surveycode=st.sssurveycode 
where s.sschainnumber= 4516 and s.ssstorenumber = 1392
result: could not prepare statement (1 ambiguous column name: s._id)
note: st.ssurveycode was not detected as a missing field.

select s.title from surveys s inner join surveystores s on s.surveycode=s.sssurveycode 
where s.sschainnumber= 4516 and s.ssstorenumber = 1392
result: ok. since "_id" was not implied in the final field list

select s._iD from surveys s limit 1
result: table with column as "_id"
note: so it means, non subselects follow the builtin fieldname, even if a different casing is convention and an alias was used.

select * FROM(select _iD from surveys s limit 1)x
result: table with _iD as field
note: fields from subselects follows the name specified by the user.

select x._Id FROM(select _iD from surveys s limit 1)x
result: table with x._Id as field
note: fields from subselects follows the name specified by the user.

select _id as ID from surveys limit 1
result: table with ID as column
note: follows the user-specified name if with "as"

select a, b
from (
			select 1 as 'a', 'a' as 'b' 
union all select 1, 'b'
union all select 2, 'a'
)
group by a
order by b desc


-ok
select * from(select _id, 1 as 'b' from surveys limit 1), (select _id, 2 as 'c' from surveys limit 1) limit 1

-ok
select * from(select _id, 1 as 'b' from surveys limit 1), (select _id, 2 as 'c' from surveys limit 1)s limit 1

-ok
select * from(select _id, 1 as 'b' from surveys limit 1)s, (select _id, 2 as 'c' from surveys limit 1) limit 1

error
select * from(select _id, 1 as 'b' from surveys limit 1)s, (select _id, 2 as 'c' from surveys limit 1)s limit 1

-ok
select s.b from(select _id, 1 as 'b' from surveys limit 1)s, (select _id, 2 as 'c' from surveys limit 1)s limit 1

select s.* from(select s._Id from(select _id from surveys limit 1)s)s

select * from(select -1 as 'a', * from (select 0 as 'a', 6 as 'a', * from(select 1 as 'a', 2 as 'a', 3 as 'a' from surveys)x)x)x

{a : {aliasSourceUC : ''}, 'x.a' : {isQualified : true}}
{a : {aliasSourceUC : ''}, 'x.a' : {isQualified : true}}
{a : {aliasSourceUC : ''}, 'x.a' : {isQualified : true}}


{a : {aliasSourceUC : ''}}
{a : {aliasSourceUC : ''}}

{a : {isAmb : true}, a:1 : {}}



{a : {aliasSourceUC : ''}}


{a : {aliasSourceUC : 's'}, s.a : {isQualified : true}}
{a : {aliasSourceUC : 's'}, s.a : {isQualified : true}}

{a : {isAmb : true}, 's.a' : {isQualified : true}}

{a : {aliasSourceUC : 's'}}

*/